package com.codecrane.core.web;


import com.codecrane.core.util.DateUtils;
import com.sea.data.entity.OrderDetail;
import com.sea.data.entity.SalesForecast;
import com.sea.finance.entity.*;
import com.sea.finance.service.VtService;
import com.sea.operate.entity.Related;
import com.sea.operate.entity.RelatedSku;
import com.sea.operate.entity.RelatedSkuPrice;
import com.sea.operate.entity.WordClick;
import com.sea.physical.entity.*;
import com.sea.product.entity.Product;
import com.sea.supply.entity.*;
import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * @author 陈鸿飞
 * 批量导入流量util工具类
 */
@Component
public class BatchImportUtils implements ApplicationContextAware {


    private static ApplicationContext context;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        context = applicationContext;
    }

    public static <T> T getBean(Class<T> clazz) {
        return context.getBean(clazz);
    }



    public static Workbook getWorkBook(InputStream is, String filePath) throws IOException {
        Workbook workbook = null;
        if (filePath.endsWith("xls")) {
            workbook = new HSSFWorkbook(is);
        } else if (filePath.endsWith("xlsx")) {
            workbook = new XSSFWorkbook(is);
        }
        else if (filePath.endsWith("csv")) {
            workbook = new XSSFWorkbook(is);
        }
        return workbook;
    }

    public static List<Sheet> getSheets(Workbook book) {
        int numberOfSheets = book.getNumberOfSheets();
        List<Sheet> sheets = new ArrayList<Sheet>();
        for (int i = 0; i < numberOfSheets; i++) {
            sheets.add(book.getSheetAt(i));
        }
        return sheets;
    }

    public static  boolean isRowEmpty(Row row){
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != CellType.BLANK){
                return false;
            }
        }
        return true;
    }

    /**
     * 导入SKU价格变动记录
     * @param sheet
     * @return
     */
    public static List<Product> iteratorForProduct(Sheet sheet) throws ParseException {
        DataFormatter dataFormatter = new DataFormatter();
        List<Product> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            Product product=new Product();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        product.setProductSku(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        product.setProductPropertyName(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        product.setProductName(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.STRING);
                        product.setProductColorName(cell.getStringCellValue());
                        break;
                    case 4:
                        cell.setCellType(CellType.STRING);
                        product.setProductBigClassName(cell.getStringCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.STRING);
                        product.setProductMidClassName(cell.getStringCellValue());
                        break;
                    case 6:
                        cell.setCellType(CellType.STRING);
                        product.setProductClassName(cell.getStringCellValue());
                        break;
                    case 7:
                        cell.setCellType(CellType.STRING);
                        product.setProductSizeName(cell.getStringCellValue());
                        break;
                    case 8:
                        cell.setCellType(CellType.STRING);
                        product.setProductCm(cell.getStringCellValue());
                        break;
                    case 9:
                        cell.setCellType(CellType.STRING);
                        product.setProductInch(cell.getStringCellValue());
                        break;
                    case 10:
                        cell.setCellType(CellType.NUMERIC);
                        product.setProductGrossWeight(cell.getNumericCellValue());
                        break;
                    case 11:
                        cell.setCellType(CellType.NUMERIC);
                        product.setProductWeight(cell.getNumericCellValue());
                        break;
                    case 12:
                        cell.setCellType(CellType.STRING);
                        product.setProductPackageNumber(Integer.parseInt(cell.getStringCellValue()));
                        break;
                    case 13:
                        cell.setCellType(CellType.STRING);
                        product.setProductContainCount(Integer.parseInt(cell.getStringCellValue()));
                        break;
                    case 14:
                        cell.setCellType(CellType.NUMERIC);
                        product.setProductOutBoxWeight(cell.getNumericCellValue());
                        break;
                    case 15:
                        cell.setCellType(CellType.NUMERIC);
                        product.setProductOutBoxLength(cell.getNumericCellValue());
                        break;
                    case 16:
                        cell.setCellType(CellType.NUMERIC);
                        product.setProductOutBoxWidth(cell.getNumericCellValue());
                        break;
                    case 17:
                        cell.setCellType(CellType.NUMERIC);
                        product.setProductOutBoxHeight(cell.getNumericCellValue());
                        break;
                    case 18:
                        cell.setCellType(CellType.NUMERIC);
                        product.setProductOutBoxVolume(cell.getNumericCellValue());
                        break;
                    case 19:
                        cell.setCellType(CellType.NUMERIC);
                        product.setProductVolume(cell.getNumericCellValue());
                        break;
                    case 20:
                        cell.setCellType(CellType.NUMERIC);
                        product.setProductLength(cell.getNumericCellValue());
                        break;
                    case 21:
                        cell.setCellType(CellType.NUMERIC);
                        product.setProductWidth(cell.getNumericCellValue());
                        break;
                    case 22:
                        cell.setCellType(CellType.NUMERIC);
                        product.setProductHeight(cell.getNumericCellValue());
                        break;
                    case 23:
                        cell.setCellType(CellType.STRING);
                        product.setProductUnit(cell.getStringCellValue());
                        break;
                    case 24:
                        cell.setCellType(CellType.STRING);
                        product.setProductCode(cell.getStringCellValue());
                        break;
                    case 25:
                        cell.setCellType(CellType.STRING);
                        product.setProductDeclarationName(cell.getStringCellValue());
                        break;
                    case 26:
                        cell.setCellType(CellType.STRING);
                        product.setProductUpc(dataFormatter.formatCellValue(cell));
                        break;
                    case 27:
                        cell.setCellType(CellType.STRING);
                        product.setProductDay(Integer.parseInt(cell.getStringCellValue()));
                        break;
                    case 28:
                        cell.setCellType(CellType.STRING);
                        product.setProductFabric(cell.getStringCellValue());
                        break;
                    case 29:
                        cell.setCellType(CellType.STRING);
                        product.setProductMaterial(cell.getStringCellValue());
                        break;
                    case 30:
                        cell.setCellType(CellType.STRING);
                        product.setProductEnName(cell.getStringCellValue());
                        break;
                    case 31:
                        cell.setCellType(CellType.STRING);
                        product.setProductComposition(cell.getStringCellValue());
                        break;
                }

            }
            list.add(product);
        }
        return list;
    }

    /**
     * 导入SKU价格变动记录
     * @param sheet
     * @return
     */
    public static List<ProductPrice> iteratorForProductPrice(Sheet sheet) throws ParseException {
        DataFormatter dataFormatter = new DataFormatter();
        List<ProductPrice> list=new ArrayList<ProductPrice>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            ProductPrice productPrice=new ProductPrice();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        productPrice.setProductSupplierName(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        productPrice.setProductChannelName(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        productPrice.setProductSku(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.NUMERIC);
                        productPrice.setProductPrice(cell.getNumericCellValue());
                        break;
                    case 4:
                        cell.setCellType(CellType.STRING);
                        productPrice.setProductRemark(cell.getStringCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.STRING);
                        productPrice.setProductMoneyType(cell.getStringCellValue());
                        break;
                    case 6:
                        productPrice.setProductPercent(Double.parseDouble(dataFormatter.formatCellValue(cell)));
                        break;
                }
            }
            list.add(productPrice);
        }
        return list;
    }

    /**
     * 导入SKU价格变动记录
     * @param sheet
     * @return
     */
    public static List<VtRelated> iteratorForVtRelated(Sheet sheet) throws ParseException {

        List<VtRelated> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            VtRelated vtRelated=new VtRelated();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        vtRelated.setListingName(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        vtRelated.setSku(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        vtRelated.setCountryName(cell.getStringCellValue());
                        break;
                }
            }
            list.add(vtRelated);
        }
        return list;
    }


    /**
     * 导入请购单明细数据
     * @param sheet
     * @return
     */
    public static List<OrderDetail> iteratorForOrderDetail(Sheet sheet) throws ParseException {
        List<OrderDetail> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            OrderDetail orderDetail=new OrderDetail();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        System.out.println(cell.getStringCellValue());
                        cell.setCellType(CellType.STRING);
                        orderDetail.setOrderChannelName(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        orderDetail.setOrderSku(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        orderDetail.setOrderNumber(Integer.parseInt(cell.getStringCellValue()+""));
                        break;
                    case 3:
                        cell.setCellType(CellType.STRING);
                        String stringCellValue = cell.getStringCellValue();
                        String orderDate = DateUtils.dateToString(DateUtils.stringToDate(stringCellValue, "yyyy-MM-dd"), "yyyy-MM-dd");
                        orderDetail.setOrderDate(orderDate);
                        break;
                }
            }
            list.add(orderDetail);
        }
        return list;
    }


    /**
     * 导入对应表Listing
     * @param sheet
     * @return
     */
    public static List<Related> iteratorForRelated(Sheet sheet) throws ParseException {
        List<Related> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            Related related=new Related();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        related.setRelatedListing(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        related.setRelatedAsin(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        related.setRelatedChannelName(cell.getStringCellValue());
                        break;
                }
            }
            list.add(related);
        }
        return list;
    }

    /**
     * 导入对应表Listing
     * @param sheet
     * @return
     */
    public static List<WordClick> iteratorForWordClick(Sheet sheet) throws ParseException {
        DataFormatter dataFormatter = new DataFormatter();
        List<WordClick> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        String asin="";
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() == 0) {
                Cell cell = nextRow.getCell(0);
                 asin= cell.getStringCellValue();
                continue;
            }
            if (nextRow.getRowNum() == 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            WordClick wordClick=new WordClick();
            while (cellIterator.hasNext()){
                           Cell cell = cellIterator.next();
                wordClick.setAsin(asin.substring(7,17));
                switch (cell.getColumnIndex()){
                    case 0:
                        wordClick.setSearch_query(dataFormatter.formatCellValue(cell));
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        wordClick.setSearch_query_score(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        wordClick.setSearch_query_volume(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.STRING);
                        wordClick.setImpressions_total_count(cell.getStringCellValue());
                        break;
                    case 4:
                        cell.setCellType(CellType.STRING);
                        wordClick.setImpressions_asin_count(cell.getStringCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.STRING);
                        wordClick.setImpressions_asin_share(cell.getStringCellValue());
                        break;

                    case 6:
                        cell.setCellType(CellType.STRING);
                        wordClick.setClicks_total_count(cell.getStringCellValue());
                        break;
                    case 7:
                        cell.setCellType(CellType.STRING);
                        wordClick.setClicks_Click_Rate(cell.getStringCellValue());
                        break;
                    case 8:
                        cell.setCellType(CellType.STRING);
                        wordClick.setClicks_ASIN_Count(cell.getStringCellValue());
                        break;
                    case 9:
                        cell.setCellType(CellType.STRING);
                        wordClick.setClicks_ASIN_Share(cell.getStringCellValue());
                        break;
                    case 10:
                        cell.setCellType(CellType.STRING);
                        wordClick.setClicks_Price(cell.getStringCellValue());
                        break;
                    case 11:
                        cell.setCellType(CellType.STRING);
                        wordClick.setClicks_ASIN_Price(cell.getStringCellValue());
                        break;
                    case 12:
                        cell.setCellType(CellType.STRING);
                        wordClick.setClicks_Same_Day_Shipping_Speed(cell.getStringCellValue());
                        break;
                    case 13:
                        cell.setCellType(CellType.STRING);
                        wordClick.setClicks_1D_Shipping_Speed(cell.getStringCellValue());
                        break;
                    case 14:
                        cell.setCellType(CellType.STRING);
                        wordClick.setClicks_2D_Shipping_Speed(cell.getStringCellValue());
                        break;
                    case 15:
                        cell.setCellType(CellType.STRING);
                        wordClick.setCart_Adds_Total_Count(cell.getStringCellValue());
                        break;
                    case 16:
                        cell.setCellType(CellType.STRING);
                        wordClick.setCart_Adds_Cart_Add_Rate(cell.getStringCellValue());
                        break;
                    case 17:
                        cell.setCellType(CellType.STRING);
                        wordClick.setCart_Adds_ASIN_Count(cell.getStringCellValue());
                        break;
                    case 18:
                        cell.setCellType(CellType.STRING);
                        wordClick.setCart_Adds_ASIN_Share(cell.getStringCellValue());
                        break;
                    case 19:
                        cell.setCellType(CellType.STRING);
                        wordClick.setCart_Adds_Price(cell.getStringCellValue());
                        break;
                    case 20:
                        cell.setCellType(CellType.STRING);
                        wordClick.setCart_Adds_ASIN_Price(cell.getStringCellValue());
                        break;
                    case 21:
                        cell.setCellType(CellType.STRING);
                        wordClick.setCart_Adds_Same_Day_Shipping_Speed(cell.getStringCellValue());
                        break;
                    case 22:
                        cell.setCellType(CellType.STRING);
                        wordClick.setCart_Adds_1D_Shipping_Speed(cell.getStringCellValue());
                        break;
                    case 23:
                        cell.setCellType(CellType.STRING);
                        wordClick.setCart_Adds_2D_Shipping_Speed(cell.getStringCellValue());
                        break;
                    case 24:
                        cell.setCellType(CellType.STRING);
                        wordClick.setPurchases_Purchase_Rate(cell.getStringCellValue());
                        break;
                    case 25:
                        cell.setCellType(CellType.STRING);
                        wordClick.setPurchases_ASIN_Count(cell.getStringCellValue());
                        break;
                    case 26:
                        cell.setCellType(CellType.STRING);
                        wordClick.setPurchases_ASIN_Share(cell.getStringCellValue());
                        break;
                    case 27:
                        cell.setCellType(CellType.STRING);
                        wordClick.setPurchases_Price(cell.getStringCellValue());
                        break;
                    case 28:
                        cell.setCellType(CellType.STRING);
                        wordClick.setPurchases_ASIN_Price(cell.getStringCellValue());
                        break;
                    case 29:
                        cell.setCellType(CellType.STRING);
                        wordClick.setPurchases_Same_Day_Shipping_Speed(cell.getStringCellValue());
                        break;
                    case 30:
                        cell.setCellType(CellType.STRING);
                        wordClick.setPurchases_1D_Shipping_Speed(cell.getStringCellValue());
                        break;
                    case 31:
                        cell.setCellType(CellType.STRING);
                        wordClick.setPurchases_2D_Shipping_Speed(cell.getStringCellValue());
                        break;
                    case 32:
                        cell.setCellType(CellType.STRING);
                        wordClick.setReporting_Date(cell.getStringCellValue());
                        break;
                }
            }
            list.add(wordClick);
        }
        return list;
    }



















    /**
     * 导入对应表Listing
     * @param sheet
     * @return
     */
    public static List<RelatedSku> iteratorForSku(Sheet sheet) throws ParseException {
        List<RelatedSku> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            RelatedSku relatedSku=new RelatedSku();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        relatedSku.setRelatedChannelName(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        relatedSku.setRelatedSku(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        relatedSku.setRelatedFnSku(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.STRING);
                        relatedSku.setRelatedAsin(cell.getStringCellValue());
                        break;
                    case 4:
                        cell.setCellType(CellType.STRING);
                        relatedSku.setRelatedListingNameEn(cell.getStringCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.STRING);
                        relatedSku.setRelatedProductNameEn(cell.getStringCellValue());
                        break;
                    case 6:
                        cell.setCellType(CellType.STRING);
                        relatedSku.setRelatedOperateName(cell.getStringCellValue());
                        break;
                }
            }
            list.add(relatedSku);
        }
        return list;
    }

    /**
     * 导入对应表Listing
     * @param sheet
     * @return
     */
    public static List<RelatedSkuPrice> iteratorForSkuPrice(Sheet sheet) throws ParseException {
        List<RelatedSkuPrice> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            RelatedSkuPrice relatedSkuPrice=new RelatedSkuPrice();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        relatedSkuPrice.setRelatedChannelName(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        relatedSkuPrice.setRelatedSku(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.NUMERIC);
                        relatedSkuPrice.setRelatedPrice(cell.getNumericCellValue());
                        break;

                }
            }
            list.add(relatedSkuPrice);
        }
        return list;
    }


    /**
     * 导入对应表Listing
     * @param sheet
     * @return
     */
    public static List<RelatedSkuPrice> iteratorForSalesPrice(Sheet sheet) throws ParseException {
        List<RelatedSkuPrice> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            RelatedSkuPrice relatedSkuPrice=new RelatedSkuPrice();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        relatedSkuPrice.setRelatedChannelName(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        relatedSkuPrice.setRelatedSku(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.STRING);
                        relatedSkuPrice.setRelatedAsin(cell.getStringCellValue());
                        break;
                    case 6:
                        cell.setCellType(CellType.NUMERIC);
                        relatedSkuPrice.setRelatedSalesPrice(cell.getNumericCellValue());
                        break;

                }
            }
            list.add(relatedSkuPrice);
        }
        return list;
    }

    /**
     * 导入收货单明细数据
     * @param sheet
     * @return
     */
    public static List<Receive> iteratorForReceive(Sheet sheet) throws ParseException {
        List<Receive> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            Receive receive=new Receive();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        receive.setReceiveNo(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        receive.setReceiveChannelName(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        receive.setReceiveSku(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.NUMERIC);
                        receive.setReceiveNumber((int)cell.getNumericCellValue());
                        break;
                    case 4:
                        cell.setCellType(CellType.STRING);
                        receive.setReceiveDate(cell.getStringCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.STRING);
                        receive.setReceivePlanNo(cell.getStringCellValue());
                        break;
                    case 6:
                        cell.setCellType(CellType.STRING);
                        receive.setOrderDate(cell.getStringCellValue());
                        break;
                    case 7:
                        cell.setCellType(CellType.STRING);
                        receive.setReceiveWare(cell.getStringCellValue());
                        break;
                }
            }
            list.add(receive);
        }
        return list;
    }

    /**
     * 导入收货单明细数据
     * @param sheet
     * @return
     */
    public static List<WareProduce> iteratorForReceiveWare(Sheet sheet) throws ParseException {
        List<WareProduce> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            WareProduce receive=new WareProduce();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        receive.setProduceNo(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        receive.setReceiveChannelName(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        receive.setReceiveSku(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.NUMERIC);
                        receive.setReceiveNumber((int)cell.getNumericCellValue());
                        break;
                    case 4:
                        cell.setCellType(CellType.STRING);
                        receive.setOrderDate(cell.getStringCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.STRING);
                        receive.setReceiveDate(cell.getStringCellValue());
                        break;
                    case 6:
                        cell.setCellType(CellType.STRING);
                        receive.setWareName(cell.getStringCellValue());
                        break;
                }
            }
            list.add(receive);
        }
        return list;
    }


    /**
     * 导入收货单明细数据
     * @param sheet
     * @return
     */
    public static List<WareBalance> iteratorForWareBalance(Sheet sheet) throws ParseException {
        List<WareBalance> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            WareBalance wareBalance=new WareBalance();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        wareBalance.setProduceNo(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        wareBalance.setReceiveChannelName(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        wareBalance.setReceiveSku(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.NUMERIC);
                        wareBalance.setReceiveNumber((int)cell.getNumericCellValue());
                        break;
                    case 4:
                        cell.setCellType(CellType.STRING);
                        wareBalance.setReceiveRemark(cell.getStringCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.STRING);
                        wareBalance.setWareName(cell.getStringCellValue());
                        break;

                }
            }
            list.add(wareBalance);
        }
        return list;
    }

    /**
     * 导入收货单明细数据
     * @param sheet
     * @return
     */
    public static List<WareChange> iteratorForWareChange(Sheet sheet) throws ParseException {
        List<WareChange> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            WareChange wareChange=new WareChange();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        wareChange.setChannelName(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        wareChange.setSku(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.NUMERIC);
                        wareChange.setChangeNumber((int)cell.getNumericCellValue());
                        break;
                }
            }
            list.add(wareChange);
        }
        return list;
    }



    /**
     * 发运计划明细
     * @param sheet
     * @return
     * @throws Exception
     */
    public static  List<DistributeSku> iteratorForDistributeSku(Sheet sheet) throws Exception{
        List<DistributeSku> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            DistributeSku distributeSku=new DistributeSku();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        distributeSku.setReceiveNo(cell.getStringCellValue());
                        break;
                    case 1:
                        distributeSku.setProduceSupplierName(cell.getStringCellValue());
                        break;
                    case 2:

                        distributeSku.setReceiveChannelName(cell.getStringCellValue());
                        break;
                    case 3:
                        distributeSku.setReceiveSku(cell.getStringCellValue());
                        break;
                    case 4:

                        break;
                    case 5:

                        break;
                    case 6:

                        break;
                    case 7:

                        break;
                    case 8:
                        double numericCellValue = cell.getNumericCellValue();
                        distributeSku.setReceiveNumber((int) numericCellValue);
                        break;
                    case 9:
                        distributeSku.setOrderDate(cell.getStringCellValue());
                        break;

                    default:
                }
            }
            list.add(distributeSku);
        }
        return list;
    }

    public static  List<DistributeSku> iteratorForAutoDistributeSku(Sheet sheet) throws Exception{
        List<DistributeSku> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            DistributeSku distributeSku=new DistributeSku();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        distributeSku.setReceiveNo(cell.getStringCellValue());
                        break;
                    case 1:
                        distributeSku.setProduceSupplierName(cell.getStringCellValue());
                        break;
                    case 2:
                        distributeSku.setReceiveChannelName(cell.getStringCellValue());
                        break;
                    case 3:
                        distributeSku.setReceiveSku(cell.getStringCellValue());
                        break;
                    case 4:
                        double numericCellValue = cell.getNumericCellValue();
                        distributeSku.setReceiveNumber((int) numericCellValue);
                        break;
                    case 5:
                        distributeSku.setOrderDate(cell.getStringCellValue());
                        break;
                    default:
                }
            }
            list.add(distributeSku);
        }
        return list;
    }

    /**
     * 分配计划明细
     * @param sheet
     * @return
     * @throws Exception
     */
    public static  List<CreateSku> iteratorForCreateSku(Sheet sheet) throws Exception{
        List<CreateSku> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            CreateSku createSku=new CreateSku();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        createSku.setCreateChannelName(cell.getStringCellValue());
                        break;
                    case 1:
                        createSku.setCreateSku(cell.getStringCellValue());
                        break;
                    case 2:
                        double createPackage = cell.getNumericCellValue();
                        createSku.setCreatePackage((int)createPackage);
                        break;
                    case 3:
                        double numericCellValue = cell.getNumericCellValue();
                        createSku.setCreateNumber((int) numericCellValue);
                        break;
                    case 4:
                        cell.setCellType(CellType.NUMERIC);
                        createSku.setCreateBoxLength(cell.getNumericCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.NUMERIC);
                        createSku.setCreateBoxWeight(cell.getNumericCellValue());
                        break;
                    case 6:
                        cell.setCellType(CellType.NUMERIC);
                        createSku.setCreateBoxHeight(cell.getNumericCellValue());
                        break;
                    case 7:
                        cell.setCellType(CellType.NUMERIC);
                        createSku.setCreateVolume(cell.getNumericCellValue());
                        break;
                    case 8:
                        cell.setCellType(CellType.NUMERIC);
                        createSku.setCreateWeight(cell.getNumericCellValue());
                        break;
                    case 9:
                        createSku.setCreateShipmentId(cell.getStringCellValue());
                        break;
                    case 10:
                        createSku.setCreateWareName(cell.getStringCellValue());
                        break;
                    default:
                }
            }
            list.add(createSku);
        }
        return list;
    }

    /**
     * 分配计划明细
     * @param sheet
     * @return
     * @throws Exception
     */
    public static  List<CreatePlan> iteratorForCreatePlan(Sheet sheet) throws Exception{
        List<CreatePlan> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            CreatePlan createPlan=new CreatePlan();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        createPlan.setCreatePlanNo(cell.getStringCellValue());
                        break;
                    case 1:
                        createPlan.setCreatePlanCabinetName(cell.getStringCellValue());
                        break;
                    case 2:
                        createPlan.setCreatePlanTransport(cell.getStringCellValue());
                        break;
                    case 3:
                        createPlan.setCreatePlanCountry(cell.getStringCellValue());
                        break;

                    case 4:
                        createPlan.setCreatePlanPlace(cell.getStringCellValue());
                        break;
                    case 5:
                        createPlan.setCreatePlanPlaceDate(cell.getStringCellValue());
                        break;
                    case 6:
                        createPlan.setCreatePlanPrice(cell.getNumericCellValue());
                        break;

                    default:
                }
            }
            list.add(createPlan);
        }
        return list;
    }


    /**
     * 分配计划明细
     * @param sheet
     * @return
     * @throws Exception
     */
    public static  List<CreateSku> iteratorForCreatePlanSku(Sheet sheet) throws Exception{
        List<CreateSku> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            CreateSku createSku=new CreateSku();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        createSku.setCreateChannelName(cell.getStringCellValue());
                        break;
                    case 1:
                        createSku.setCreateSku(cell.getStringCellValue());
                        break;
                    case 2:
                        double createPackage = cell.getNumericCellValue();
                        createSku.setCreatePackage((int)createPackage);
                        break;
                    case 3:
                        double numericCellValue = cell.getNumericCellValue();
                        createSku.setCreateNumber((int) numericCellValue);
                        break;
                    case 4:
                        cell.setCellType(CellType.NUMERIC);
                        createSku.setCreateBoxLength(cell.getNumericCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.NUMERIC);
                        createSku.setCreateBoxWeight(cell.getNumericCellValue());
                        break;
                    case 6:
                        cell.setCellType(CellType.NUMERIC);
                        createSku.setCreateBoxHeight(cell.getNumericCellValue());
                        break;
                    case 7:
                        cell.setCellType(CellType.NUMERIC);
                        createSku.setCreateVolume(cell.getNumericCellValue());
                        break;
                    case 8:
                        cell.setCellType(CellType.NUMERIC);
                        createSku.setCreateWeight(cell.getNumericCellValue());
                        break;
                    case 9:
                        createSku.setCreateShipmentId(cell.getStringCellValue());
                        break;
                    case 10:
                        createSku.setCreateWareName(cell.getStringCellValue());
                        break;
                    case 12:
                        System.out.println("柜号:"+cell.getStringCellValue());
                        createSku.setCreatePlanNo(cell.getStringCellValue());
                        break;
                    default:
                }
            }
            list.add(createSku);
        }
        return list;
    }


    /**
     * 分配计划明细
     * @param sheet
     * @return
     * @throws Exception
     */
    public static  List<CreatePackingSku> iteratorForCreatePackingSku(Sheet sheet) throws Exception{
        List<CreatePackingSku> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            CreatePackingSku createPackingSku=new CreatePackingSku();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.NUMERIC);
                        double n1 = cell.getNumericCellValue();
                        createPackingSku.setCreateBoxNumber((int) n1);
                        break;
                    case 1:
                        createPackingSku.setCreateChannelName(cell.getStringCellValue());
                        break;
                    case 2:
                        createPackingSku.setCreateSku(cell.getStringCellValue());
                        break;
                    case 7:
                        cell.setCellType(CellType.NUMERIC);
                        double n2 = cell.getNumericCellValue();
                        createPackingSku.setCreateNumber((int) n2);
                        break;
                    case 8:
                        cell.setCellType(CellType.NUMERIC);
                        double n3 = cell.getNumericCellValue();
                        createPackingSku.setCreateNumberTotal((int) n3*createPackingSku.getCreateNumber());
                        break;
                    case 10:
                        cell.setCellType(CellType.NUMERIC);
                        createPackingSku.setCreateBoxWeight(cell.getNumericCellValue());
                        break;
                    case 11:
                        cell.setCellType(CellType.NUMERIC);
                        createPackingSku.setCreateBoxKg(cell.getNumericCellValue());
                        break;
                    case 12:
                        cell.setCellType(CellType.NUMERIC);
                        createPackingSku.setCreateBoxVolume(cell.getNumericCellValue());
                        break;
                    case 13:
                        cell.setCellType(CellType.STRING);
                        createPackingSku.setCreateShipmentId(cell.getStringCellValue());
                        break;
                    default:
                }
            }
            list.add(createPackingSku);
        }
        return list;
    }



    /**
     * Payment明细
     * @param sheet
     * @return
     * @throws Exception
     */
    public static  List<Payment> iteratorForPayment(Sheet sheet) throws Exception{
        List<Payment> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            Payment payment=new Payment();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        payment.setPaymentDate(cell.getStringCellValue());
                        break;
                    case 1:
                        payment.setPaymentSku(cell.getStringCellValue());
                        break;
                    case 2:
                        payment.setPaymentChannelName(cell.getStringCellValue());
                        break;
                    case 3:
                        double numericCellValue = cell.getNumericCellValue();
                        payment.setPaymentNumber((int) numericCellValue);
                        break;
                    default:
                }
            }
            list.add(payment);
        }
        return list;
    }


    public static  List<ProducePayment> iteratorForDealPayment(Sheet sheet) throws Exception{
        DataFormatter dataFormatter = new DataFormatter();
        List<ProducePayment> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            ProducePayment payment=new ProducePayment();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        payment.setProduceNo(cell.getStringCellValue());
                        break;
                    case 1:
                        double numericCellValue = cell.getNumericCellValue();
                        payment.setProducePayment(numericCellValue);
                        break;
                    case 2:
                        payment.setProduceRemark(cell.getStringCellValue());
                        break;
                    case 3:
                        payment.setCreatePlanNo(dataFormatter.formatCellValue(cell));
                        break;
                    default:
                }
            }
            list.add(payment);
        }
        return list;
    }

    /**
     * 导入付款明细
     * @param sheet
     * @return
     * @throws Exception
     */
    public static  List<ProducePayment> iteratorForPaymentDetail(Sheet sheet) throws Exception{
        List<ProducePayment> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            ProducePayment payment=new ProducePayment();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        payment.setProduceNo(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.NUMERIC);
                        payment.setProducePayment(cell.getNumericCellValue());
                        break;
                    case 2:
                        payment.setProduceIsCheck(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.NUMERIC);
                        payment.setProduceCheckMoney(cell.getNumericCellValue());
                        break;
                    case 4:
                        cell.setCellType(CellType.NUMERIC);
                        payment.setProduceUnusualMoney(cell.getNumericCellValue());
                        break;
                    case 5:
                        payment.setProduceOutTime(cell.getStringCellValue());
                        break;
                    case 6:
                        payment.setCreatePlanNo(cell.getStringCellValue());
                        break;
                    case 7:
                        payment.setProduceReason(cell.getStringCellValue());
                        break;
                    default:
                }
            }
            list.add(payment);
        }
        return list;
    }

    /**
     * Payment明细
     * @param sheet
     * @return
     * @throws Exception
     */
    public static  List<PaymentAgain> iteratorForPaymentAgain(Sheet sheet) throws Exception{
        List<PaymentAgain> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            PaymentAgain payment=new PaymentAgain();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        payment.setPaymentDate(cell.getStringCellValue());
                        break;
                    case 1:
                        payment.setPaymentSku(cell.getStringCellValue());
                        break;
                    case 2:
                        payment.setPaymentChannelName(cell.getStringCellValue());
                        break;
                    case 3:
                        double numericCellValue = cell.getNumericCellValue();
                        payment.setPaymentNumber((int) numericCellValue);
                        break;
                    default:
                }
            }
            list.add(payment);
        }
        return list;
    }

    /**
     * Payment明细
     * @param sheet
     * @return
     * @throws Exception
     */
    public static  List<PaymentBalance> iteratorForPaymentBalance(Sheet sheet) throws Exception{
        List<PaymentBalance> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            PaymentBalance payment=new PaymentBalance();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        payment.setPaymentDate(cell.getStringCellValue());
                        break;
                    case 1:
                        payment.setPaymentSku(cell.getStringCellValue());
                        break;
                    case 2:
                        payment.setPaymentChannelName(cell.getStringCellValue());
                        break;
                    case 3:
                        double numericCellValue = cell.getNumericCellValue();
                        payment.setPaymentNumber((int) numericCellValue);
                        break;
                    default:
                }
            }
            list.add(payment);
        }
        return list;
    }



    /**
     * 导入库存挑拨SKU明细
     * @param sheet
     * @return
     */
    public static List<StockCreateSku> iteratorForStockCreateSku(Sheet sheet){
        List<StockCreateSku> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            StockCreateSku stockCreateSku=new StockCreateSku();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        stockCreateSku.setCreateShipmentId(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        stockCreateSku.setCreateSku(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.NUMERIC);
                        stockCreateSku.setCreateNumber((int) cell.getNumericCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.NUMERIC);
                        stockCreateSku.setCreatePackage((int) cell.getNumericCellValue());
                        break;
                    case 4:
                        cell.setCellType(CellType.NUMERIC);
                        stockCreateSku.setCreateLength(cell.getNumericCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.NUMERIC);
                        stockCreateSku.setCreateWidth(cell.getNumericCellValue());
                        break;
                    case 6:
                        cell.setCellType(CellType.NUMERIC);
                        stockCreateSku.setCreateHeight(cell.getNumericCellValue());
                        break;
                    case 7:
                        cell.setCellType(CellType.NUMERIC);
                        stockCreateSku.setCreateVolume(cell.getNumericCellValue());
                        break;
                    default:
                }
            }
            list.add(stockCreateSku);
        }
        return list;
    }

    /**
     * 导入请购单明细数据
     * @param sheet
     * @return
     */
    public static List<SalesForecast> iteratorForSalesForecast(Sheet sheet) throws ParseException {
        List<SalesForecast> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            SalesForecast salesForecast=new SalesForecast();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        salesForecast.setSalesFnsku(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        salesForecast.setSalesAsin(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        salesForecast.setSalesChannelName(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.STRING);
                        salesForecast.setSalesSku(cell.getStringCellValue());
                        break;
                    case 4:
                        cell.setCellType(CellType.STRING);
                        salesForecast.setSalesDate(cell.getStringCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.NUMERIC);
                        salesForecast.setSalesNumber(cell.getNumericCellValue());
                        break;
                }
            }
            list.add(salesForecast);
        }
        return list;
    }

    /**
     * 导入请购单明细数据
     * @param sheet
     * @return
     */
    public static List<SalesForecast> iteratorForSalesForecastStock(Sheet sheet) throws ParseException {
        List<SalesForecast> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            SalesForecast salesForecast=new SalesForecast();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        salesForecast.setSalesSku(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        salesForecast.setSalesFnsku(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        salesForecast.setSalesAsin(cell.getStringCellValue());
                        break;
                    case 10:
                        cell.setCellType(CellType.STRING);
                        salesForecast.setSalesStock(Double.valueOf(cell.getStringCellValue()));
                        break;

                    case 12:
                        cell.setCellType(CellType.STRING);
                        salesForecast.setSalesStock(salesForecast.getSalesStock()+Double.valueOf(cell.getStringCellValue()));
                        break;
                    case 17:
                        cell.setCellType(CellType.STRING);
                        salesForecast.setSalesStock(salesForecast.getSalesStock()+Double.valueOf(cell.getStringCellValue()));
                        break;

                }
            }
            list.add(salesForecast);
        }
        return list;
    }



    /**
     * 导入SKU采购单调整记录
     * @param sheet
     * @return
     */
    public static List<ProduceBalance> iteratorForProduce(Sheet sheet) throws ParseException {
        List<ProduceBalance> list=new ArrayList<ProduceBalance>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            ProduceBalance produceBalance=new ProduceBalance();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        produceBalance.setProduceNo(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        produceBalance.setOrderChannelName(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        produceBalance.setOrderSku(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.STRING);
                        produceBalance.setOrderDate(cell.getStringCellValue());
                        break;
                    case 4:
                        cell.setCellType(CellType.STRING);
                        produceBalance.setProduceAfterNumber(Integer.parseInt(cell.getStringCellValue()));
                        break;

                }
            }
            list.add(produceBalance);
        }
        return list;
    }

    /**
     * 导入海外仓库存
     * @param sheet
     * @return
     */
    public static List<StockProduce> iteratorForStockProduce(Sheet sheet) throws ParseException {
        List<StockProduce> list=new ArrayList<StockProduce>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            StockProduce stockProduce=new StockProduce();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        stockProduce.setStockNo(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        stockProduce.setStockWareName(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        stockProduce.setStockChannelName(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.STRING);
                        stockProduce.setStockSku(cell.getStringCellValue());
                        break;
                    case 4:
                        cell.setCellType(CellType.STRING);
                        stockProduce.setStockNumber(Integer.parseInt(cell.getStringCellValue()));
                        break;
                }
            }
            list.add(stockProduce);
        }
        return list;
    }

    /**
     * Payment明细
     * @param sheet
     * @return
     * @throws Exception
     */
    public static  List<PaymentChange> iteratorForPaymentChange(Sheet sheet) throws Exception{
        List<PaymentChange> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            PaymentChange change=new PaymentChange();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        change.setPaymentDate(cell.getStringCellValue());
                        break;
                    case 1:
                        change.setPaymentBeforeChannelName(cell.getStringCellValue());
                        break;
                    case 2:
                        change.setPaymentSkuBefore(cell.getStringCellValue());
                        break;
                    case 3:
                        change.setPaymentAfterChannelName(cell.getStringCellValue());
                        break;
                    case 4:
                        change.setPaymentSkuAfter(cell.getStringCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.STRING);
                        change.setPaymentNumber(Integer.parseInt(cell.getStringCellValue()));
                        break;
                    default:
                }
            }
            list.add(change);
        }
        return list;
    }
    /**
     * 导入对应表Listing
     * @param sheet
     * @return
     */
    public static List<Fund> iteratorForFund(Sheet sheet) throws ParseException {
        List<Fund> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            Fund fund=new Fund();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        fund.setFundDate(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        fund.setFundMoneyType(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        if(StringUtils.isNotBlank(cell.getStringCellValue())) {
                            fund.setFundMoney(Double.parseDouble(cell.getStringCellValue()));
                        }else {
                            fund.setFundMoney(0.00);
                        }
                        break;
                    case 3:
                        cell.setCellType(CellType.STRING);
                        if(StringUtils.isNotBlank(cell.getStringCellValue())) {
                            fund.setFundExpendMoney(Double.parseDouble(cell.getStringCellValue()));
                        }else{
                            fund.setFundExpendMoney(0.00);
                        }
                        break;
                    case 4:
                        cell.setCellType(CellType.STRING);
                        fund.setFundTotal(Double.parseDouble(cell.getStringCellValue()));
                        break;
                    case 5:
                        cell.setCellType(CellType.STRING);
                        fund.setFundName(cell.getStringCellValue());
                        break;
                    case 6:
                        cell.setCellType(CellType.STRING);
                        fund.setFundPlatform(cell.getStringCellValue());
                        break;
                    case 7:
                        cell.setCellType(CellType.STRING);
                        fund.setFundAccount(cell.getStringCellValue());
                        break;
                    case 8:
                        cell.setCellType(CellType.STRING);
                        fund.setFundShopName(cell.getStringCellValue());
                        break;
                }
            }
            list.add(fund);
        }
        return list;
    }




    public static  List<Vt> iteratorForVtDealSku(Sheet sheet) throws Exception{
        DataFormatter dataFormatter = new DataFormatter();
        List<Vt> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            Vt vt=new Vt();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        vt.setId(Integer.parseInt(dataFormatter.formatCellValue(cell)));
                        break;
                    case 27:
                        vt.setSku(dataFormatter.formatCellValue(cell));
                        break;
                    case 31:
                        vt.setListing_name(dataFormatter.formatCellValue(cell));
                        break;
                    default:
                }
            }
            list.add(vt);
        }
        return list;
    }

    public static  List<Vt> iteratorForVtDealAmazonOrder(Sheet sheet) throws Exception{
        DataFormatter dataFormatter = new DataFormatter();
        List<Vt> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            Vt vt=new Vt();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        vt.setOrder_id((dataFormatter.formatCellValue(cell)));
                        break;

                    default:
                }
            }
            list.add(vt);
        }
        return list;
    }



    public static  List<VtFeeMatch> iteratorForVtFeeMatch(Sheet sheet) throws Exception{
        DataFormatter dataFormatter = new DataFormatter();
        List<VtFeeMatch> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            VtFeeMatch vt=new VtFeeMatch();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        vt.setFeeName(dataFormatter.formatCellValue(cell));
                        break;
                    case 1:
                        vt.setFeeType(dataFormatter.formatCellValue(cell));
                        break;
                    default:
                }
            }
            list.add(vt);
        }
        return list;
    }

    /**
     * 导入SKU价格变动记录
     * @param sheet
     * @return
     */
    public static List<CreateDespatchFreight> iteratorForDespathchFreight(Sheet sheet) throws ParseException {
        DataFormatter dataFormatter = new DataFormatter();
        List<CreateDespatchFreight> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            CreateDespatchFreight despatchFreight=new CreateDespatchFreight();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        despatchFreight.setCreatePlanNo(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        despatchFreight.setCreateSupplier(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        despatchFreight.setCreateMoneyType(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.STRING);
                        switch (cell.getStringCellValue()) {
                            case "运费":
                                despatchFreight.setFlag(0);
                                break;
                            case "关税":
                                despatchFreight.setFlag(1);
                                break;
                            case "头程":
                                despatchFreight.setFlag(2);
                                break;
                            case "海运":
                                despatchFreight.setFlag(3);
                                break;
                            case "尾程":
                                despatchFreight.setFlag(4);
                                break;
                            default:
                                despatchFreight.setFlag(5);
                                break;
                        }

                        break;
                    case 4:
                        cell.setCellType(CellType.NUMERIC);
                        despatchFreight.setCreateMoney(cell.getNumericCellValue());
                        break;
                    case 5:
                        cell.setCellType(CellType.STRING);
                        despatchFreight.setCreatePayType(cell.getStringCellValue());
                        break;
                    case 6:
                        cell.setCellType(CellType.STRING);
                        despatchFreight.setCreateRemark(cell.getStringCellValue());
                        break;

                }
            }
            list.add(despatchFreight);
        }
        return list;
    }

    /**
     * 导入SKU申报系数
     * @param sheet
     * @return
     */
    public static List<CoefficientSku> iteratorForCoefficientSku(Sheet sheet) throws ParseException {
        List<CoefficientSku> list=new ArrayList<CoefficientSku>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            CoefficientSku coefficientSku=new CoefficientSku();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        coefficientSku.setCoefficientChannelName(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        coefficientSku.setCoefficientSku(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.NUMERIC);
                        coefficientSku.setCoefficientCoef(cell.getNumericCellValue());
                        break;

                }
            }
            list.add(coefficientSku);
        }
        return list;
    }


    /**
     * 导入明细数据
     * @param sheet
     * @return
     */
    public static List<SupplierSku> iteratorForSupplierSku(Sheet sheet) throws ParseException {
        List<SupplierSku> list=new ArrayList<>();
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()){
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() < 1) {
                continue;
            }
            boolean rowEmpty = isRowEmpty(nextRow);
            if(rowEmpty==true){
                break;
            }
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            SupplierSku supplierSku=new SupplierSku();
            while (cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()){
                    case 0:
                        cell.setCellType(CellType.STRING);
                        supplierSku.setSupplierChannelName(cell.getStringCellValue());
                        break;
                    case 1:
                        cell.setCellType(CellType.STRING);
                        supplierSku.setSupplierSku(cell.getStringCellValue());
                        break;
                    case 2:
                        cell.setCellType(CellType.STRING);
                        supplierSku.setSupplierName(cell.getStringCellValue());
                        break;
                    case 3:
                        cell.setCellType(CellType.STRING);
                        supplierSku.setPaymentType(cell.getStringCellValue());
                        break;
                }
            }
            list.add(supplierSku);
        }
        return list;
    }

}
